﻿namespace IntellectShop.Common
{
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Transactions;

	public partial class News
	{
		public static News RetrieveByNewsID(int newsID)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query = (
							from news in dataContext.NewsLists
							where news.NewsID == newsID
							select news).SingleOrDefault();

				News item = new News
				{
					NewsID = query.NewsID,
					Header = query.Header,
					Body = query.Body,
					CreatedBy = query.CreatedBy,
					Created = query.Created,
					SortingID = query.SortingID
				};

				return item;
			}
		}

		public static List<Common.News> RetrieveAll()
		{
			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
			{
				var query =
							from news in context.NewsLists
							orderby news.SortingID descending, news.Created descending, news.NewsID descending
							select news;

				List<Common.News> result = new List<News>();
				foreach (var row in query)
				{
					result.Add(new Common.News
					{
						NewsID = row.NewsID,
						Header = row.Header,
						Body = row.Body,
						CreatedBy = row.CreatedBy,
						Created = row.Created,
						SortingID = row.SortingID
					});
				}

				return result;
			}
		}

		public static List<Common.News> RetrieveTop(int count)
		{
			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
			{
				var query = (
							from news in context.NewsLists
							orderby news.SortingID descending, news.Created descending, news.NewsID descending
							select news).Take(count);

				List<Common.News> result = new List<News>();
				foreach (var row in query)
				{
					result.Add(new Common.News
					{
						NewsID = row.NewsID,
						Header = row.Header,
						Body = row.Body,
						CreatedBy = row.CreatedBy,
						Created = row.Created,
						SortingID = row.SortingID
					});
				}

				return result;
			}
		}

		public void Create(string createdBy)
		{
			this.CreatedBy = createdBy;
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var newsDB = new DB.NewsList
				{
					Header = this.Header,
					Body = this.Body,
					CreatedBy = this.CreatedBy,
					Created = DateTime.Now,
					SortingID = this.SortingID
				};
				dataContext.NewsLists.InsertOnSubmit(newsDB);
				dataContext.SubmitChanges();
			}
		}

		public void Update()
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var newsDB = (
								from news in dataContext.NewsLists
								where news.NewsID == this.NewsID
								select news).SingleOrDefault();
				newsDB.Header = this.Header;
				newsDB.Body = this.Body;
				newsDB.CreatedBy = this.CreatedBy;
				newsDB.Created = this.Created;
				newsDB.SortingID = this.SortingID;

				dataContext.SubmitChanges();
			}
		}

		public static void Delete(int newsID)
		{
			using (TransactionScope trans = new TransactionScope())
			{
				using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
				{
					var newsDB = (from news in dataContext.NewsLists where news.NewsID == newsID select news).SingleOrDefault();
					dataContext.NewsLists.DeleteOnSubmit(newsDB);
					dataContext.SubmitChanges();

					trans.Complete();
				}
			}
		}
	}
}
